USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetContactsXml')
	DROP FUNCTION dbo.GetContactsXml
GO


CREATE FUNCTION [dbo].[GetContactsXml] (
	@RPartnerGUID		VARCHAR(100)
) RETURNS VARCHAR(MAX)
AS
BEGIN
/*	
	declare 
		@xml			varchar(max)



	SET @xml		= '<CONTACTS><entry id="0" accountid=""></entry>'

	SELECT @xml = @xml + '<entry id="' + CAST(c.PartnerNo AS VARCHAR) + '"><![CDATA['+ c.PartnerFirstName + ' ' + c.PartnerLastName +']]></entry>' 
	FROM TR_RELATIONSHIPS as r
	INNER JOIN TR_PARTNER as rp on rp.PartnerNo = r.Partner2
	INNER JOIN TR_PARTNER as c on r.SourceGUID = c.PartnerGUID
	WHERE 
		CAST(rp.PartnerGUID AS VARCHAR(100)) = ISNULL(@relatedGUID, CAST(rp.PartnerGUID AS VARCHAR(100)))
	ORDER BY (c.PartnerFirstName + ' ' + c.PartnerLastName)

	set @xml = @xml + '</CONTACTS>'	

	return @xml
*/


	declare 
		@xml			varchar(max)

	SET @xml		= '<CONTACTS><entry id="0" accountid="0"></entry>'

	;WITH A AS (
		-- All Contacts From TR_PARTNER
		select 
			PartnerNo			AS ContactNo
		,	PartnerGUID			AS ContactGUID
		,	PartnerFirstName	as FirstName
		,	PartnerLastName		AS LastName
		from tr_partner 
		where
			PartnerCategory = 1
		and PartnerType = 3
	), B AS (
		-- Account Related Contacts
		SELECT
			r.Partner2			as AccountNo
		,	p.PartnerGUID		as AccountGUID
		,	p.PartnerName		as AccountName
		,	tc.ContactNo
		FROM TR_RELATIONSHIPS as r
		inner join A as tc
			on tc.ContactGUID = r.SourceGUID
		inner join TR_PARTNER as p
			on p.PartnerNo = r.Partner2
		WHERE
			r.RelationShipType = 8		-- ACCOUNT CONTACT
		AND	CAST(p.PartnerGUID AS VARCHAR(100)) = (CASE WHEN ISNULL(@RPartnerGUID,'') = '' THEN CAST(p.PartnerGUID AS VARCHAR(100)) ELSE @RPartnerGUID END)
	), TFinal AS (
		SELECT
			A.ContactNo
		,	A.ContactGUID
		,	A.FirstName
		,	A.LastName
		,	B.AccountNo		
		,	B.AccountGUID
		,	B.AccountName
		FROM	A
		INNER JOIN B
			on A.ContactNo = B.ContactNo
	)

	SELECT @xml = @xml + '<entry id="' + CAST(ContactNo AS VARCHAR) + '" accountid="'+CAST(AccountNo AS VARCHAR)+'"><![CDATA['+FirstName + ' ' + LastName+']]></entry>'
	from TFinal 
	order by (FirstName + ' ' + LastName)

	set @xml = @xml + '</CONTACTS>'	

	RETURN @xml


END


-- SELECT dbo.GetContactsXml('')